package com.singing.dao;

import com.singing.model.SingScore;
import com.singing.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SingScoreDao {

    // 根据singerId查询所有的SingScore

    public List<SingScore> selectListBySingerId(int singerId) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select * from sing_score where singer_id=?");
            ps.setInt(1, singerId);
            resultSet = ps.executeQuery();
            List<SingScore> singScores = new ArrayList<>();
            SingScore singScore = null;
            while (resultSet.next()) {
                singScore = new SingScore();
                singScore.setId(resultSet.getInt("id"));
                singScore.setSingerId(resultSet.getInt("singer_id"));
                singScore.setScore(resultSet.getInt("score"));
                singScore.setJudgeId(resultSet.getInt("judge_id"));
                singScores.add(singScore);
            }

            return singScores;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return null;
    }


    // 查询所有SingScore
    public List<SingScore> selectAll() {

        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select * from sing_score");
            resultSet = ps.executeQuery();
            List<SingScore> singScores = new ArrayList<>();
            SingScore singScore = null;
            while (resultSet.next()) {
                singScore = new SingScore();
                singScore.setId(resultSet.getInt("id"));
                singScore.setSingerId(resultSet.getInt("singer_id"));
                singScore.setScore(resultSet.getInt("score"));
                singScore.setJudgeId(resultSet.getInt("judge_id"));
                singScores.add(singScore);
            }
            return singScores;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return null;
    }

    // 批量插入
    public int insertList(List<SingScore> singScores) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        try {
            connection.setAutoCommit(false);

            ps = connection.prepareStatement("insert into sing_score(singer_id, score, judge_id) values (?, ?, ?)");
            for (SingScore singScore : singScores) {
                ps.setInt(1, singScore.getSingerId());
                ps.setInt(2, singScore.getScore());
                ps.setInt(3, singScore.getJudgeId());
                ps.addBatch();
            }

            ps.executeBatch();
            connection.commit();
            return singScores.size();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DbUtil.closeResource(connection, ps);
        }
        return 0;
    }

    public boolean exists(int singerId) {

        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select count(*) from sing_score where singer_id=?");
            ps.setInt(1, singerId);
            resultSet = ps.executeQuery();

            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                return count > 0;
            }
            return false;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return false;
    }


    // 查询所有的singer_id
    public List<Integer> selectAllSingerId() {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select distinct singer_id from sing_score");
            resultSet = ps.executeQuery();
            List<Integer> singerIds = new ArrayList<>();
            while (resultSet.next()) {
                singerIds.add(resultSet.getInt("singer_id"));
            }
            return singerIds;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return null;
    }


}
